package utils;

import java.io.FileOutputStream;
import java.util.Properties;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public class ConfigGenerator {
    public static void main(String[] args) {
        String url = "jdbc:mysql://dbconn.sealosgzg.site:37535/grade_management?useSSL=false&serverTimezone=UTC&characterEncoding=utf8&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "wfwgwrjx";
        
        try {
            Properties prop = new Properties();
            
            // 加密密码
            String encryptedPassword = PasswordUtil.encrypt(password);
            System.out.println("原始加密密码: " + encryptedPassword);
            
            prop.setProperty("db.url", url);
            prop.setProperty("db.username", username);
            prop.setProperty("db.password", "ENC(" + encryptedPassword + ")");
            
            // 保存配置
            prop.store(new FileOutputStream("config.properties"), "Database Configuration");
            
            System.out.println("配置文件生成成功！");
            System.out.println("数据库URL: " + url);
            System.out.println("用户名: " + username);
            System.out.println("加密后的密码: " + "ENC(" + encryptedPassword + ")");
            
            // 测试数据库连接
            if (DatabaseUtil.testConnection()) {
                System.out.println("数据库连接测试成功！");
                
                // 获取数据库连接并创建表
                try (Connection conn = DatabaseUtil.getConnection()) {
                    generateTables(conn);
                    System.out.println("数据库表创建完成！");
                } catch (SQLException e) {
                    System.out.println("创建数据库表失败：" + e.getMessage());
                    e.printStackTrace();
                }
            } else {
                System.out.println("数据库连接测试失败！");
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void generateTables(Connection conn) throws SQLException {
        // 创建用户表
        String createUsersTable = """
            CREATE TABLE IF NOT EXISTS users (
                id INT PRIMARY KEY AUTO_INCREMENT,
                username VARCHAR(50) UNIQUE NOT NULL,
                password VARCHAR(100) NOT NULL,
                role ENUM('admin', 'teacher', 'student') NOT NULL,
                name VARCHAR(50) NOT NULL,
                gender ENUM('男', '女') NOT NULL,
                phone VARCHAR(20),
                email VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_login TIMESTAMP NULL,
                status TINYINT DEFAULT 1 COMMENT '1:正常,0:禁用'
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        // 创建班级表
        String createClassesTable = """
            CREATE TABLE IF NOT EXISTS classes (
                id INT PRIMARY KEY AUTO_INCREMENT,
                class_name VARCHAR(50) UNIQUE NOT NULL,
                grade_year INT NOT NULL,
                teacher_id INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (teacher_id) REFERENCES users(id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        // 创建专业表
        String createMajorsTable = """
            CREATE TABLE IF NOT EXISTS majors (
                id INT PRIMARY KEY AUTO_INCREMENT,
                major_code VARCHAR(3) NOT NULL UNIQUE,
                major_name VARCHAR(50) NOT NULL,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        // 创建课程表
        String createCoursesTable = """
            CREATE TABLE IF NOT EXISTS courses (
                id INT PRIMARY KEY AUTO_INCREMENT,
                course_code VARCHAR(10) NOT NULL UNIQUE,
                course_name VARCHAR(50) NOT NULL,
                credits DECIMAL(3,1) NOT NULL,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        // 创建专业课程关联表
        String createMajorCoursesTable = """
            CREATE TABLE IF NOT EXISTS major_courses (
                major_id INT NOT NULL,
                course_id INT NOT NULL,
                is_required BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (major_id, course_id),
                FOREIGN KEY (major_id) REFERENCES majors(id) ON DELETE CASCADE,
                FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        // 创建学生档案表
        String createStudentProfilesTable = """
            CREATE TABLE IF NOT EXISTS student_profiles (
                id INT PRIMARY KEY AUTO_INCREMENT,
                student_no VARCHAR(20) NOT NULL UNIQUE,
                name VARCHAR(50) NOT NULL,
                gender ENUM('男', '女') NOT NULL,
                id_card VARCHAR(18) NOT NULL UNIQUE,
                birth_date DATE NOT NULL,
                admission_year INT NOT NULL,
                major_code VARCHAR(3) NOT NULL,
                class_no INT NOT NULL,
                phone VARCHAR(20),
                email VARCHAR(100),
                address TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (major_code) REFERENCES majors(major_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        // 创建成绩表
        String createGradesTable = """
            CREATE TABLE IF NOT EXISTS grades (
                id INT PRIMARY KEY AUTO_INCREMENT,
                student_id INT NOT NULL,
                subject_id INT NOT NULL,
                score DECIMAL(5,2) NOT NULL,
                exam_type ENUM('期中考试', '期末考试', '单元测试', '模拟考试') NOT NULL,
                exam_date DATE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
                FOREIGN KEY (subject_id) REFERENCES courses(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        try (Statement stmt = conn.createStatement()) {
            // 按顺序创建表（考虑外键依赖）
            stmt.executeUpdate(createUsersTable);
            System.out.println("用户表创建成功");
            
            stmt.executeUpdate(createClassesTable);
            System.out.println("班级表创建成功");
            
            stmt.executeUpdate(createMajorsTable);
            System.out.println("专业表创建成功");
            
            stmt.executeUpdate(createCoursesTable);
            System.out.println("课程表创建成功");
            
            stmt.executeUpdate(createMajorCoursesTable);
            System.out.println("专业课程关联表创建成功");
            
            stmt.executeUpdate(createStudentProfilesTable);
            System.out.println("学生档案表创建成功");
            
            stmt.executeUpdate(createGradesTable);
            System.out.println("成绩表创建成功");
        }
    }
} 